-- Create goals table for tracking therapeutic objectives
-- Part of FACET mental health platform

CREATE TABLE public.goals (
  id SERIAL PRIMARY KEY,
  user_id UUID NOT NULL REFERENCES public.users(id) ON DELETE CASCADE,
  title TEXT NOT NULL,
  description TEXT,
  target INTEGER NOT NULL CHECK (target BETWEEN 1 AND 100),
  current INTEGER NOT NULL DEFAULT 0 CHECK (current BETWEEN 0 AND target),
  status goal_status NOT NULL DEFAULT 'active',
  priority INTEGER DEFAULT 3 CHECK (priority BETWEEN 1 AND 5),
  deadline TIMESTAMP WITH TIME ZONE,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  completed_at TIMESTAMP WITH TIME ZONE
);

-- Create index for faster queries by user_id
CREATE INDEX idx_goals_user_id ON public.goals(user_id);

-- Create index for status filtering
CREATE INDEX idx_goals_status ON public.goals(status);

-- Add trigger to update updated_at timestamp
CREATE OR REPLACE FUNCTION update_goals_updated_at()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = NOW();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER update_goals_updated_at_trigger
BEFORE UPDATE ON public.goals
FOR EACH ROW
EXECUTE FUNCTION update_goals_updated_at();

-- Add comment to the table
COMMENT ON TABLE public.goals IS 'Stores therapeutic goals for users, allowing progress tracking and management.';

-- Add comments to columns
COMMENT ON COLUMN public.goals.title IS 'Short title describing the goal.';
COMMENT ON COLUMN public.goals.description IS 'Detailed description of the goal.';
COMMENT ON COLUMN public.goals.target IS 'Target completion percentage (1-100).';
COMMENT ON COLUMN public.goals.current IS 'Current completion percentage (0-target).';
COMMENT ON COLUMN public.goals.status IS 'Current status of the goal.';
COMMENT ON COLUMN public.goals.priority IS 'Priority level (1-5, 1 being highest).';
COMMENT ON COLUMN public.goals.deadline IS 'Target completion date.';
COMMENT ON COLUMN public.goals.completed_at IS 'Date when the goal was marked as completed.';